package com.edu.dao;

import com.edu.pojo.EduCurriculum;
import com.edu.pojo.EduStu;
import org.apache.ibatis.annotations.*;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;

/**
 * @author YJF
 */
public interface EduCurMapper extends Mapper<EduCurriculum> {

    /**
     * 根据课程名称查找集合
     *
     * @param curName  课程名称
     * @param allTecId 教师ID
     * @return 结果集
     */
    @Select("<script>" +
            "SELECT " +
            "tec_name,is_pub ,cur_id, cur_name, cur_address " +
            "FROM edu_teacher t RIGHT JOIN edu_curriculum c ON " +
            "c.tec_id = t.tec_id WHERE c.tec_id IN " +
            "<foreach item='item' index='index' collection='allTecId' " +
            "open='(' separator=',' close=')'> " +
            "#{item}" +
            "</foreach>" +
            "AND cur_name LIKE '%${curName}%'" +
            "</script>")
    @Results({
            @Result(column = "tec_name", property = "curBy"),
            @Result(column = "cur_id", property = "curId"),
            @Result(column = "is_pub", property = "isPub"),
            @Result(column = "cur_name", property = "curName"),
            @Result(column = "cur_address", property = "curAddress")
    })
    List<EduCurriculum> findByPage(String curName, @Param("allTecId") List<Integer> allTecId);

    /**
     * 根据学号查询所选课程
     *
     * @param stuId 学号
     * @return 课程列表
     */
    @Select("SELECT " +
            "sc.cur_id, cur_name, is_pub " +
            "FROM edu_stu_cur sc LEFT JOIN edu_curriculum c ON sc.cur_id = c.cur_id WHERE stu_id = #{stuId} AND is_pub = 0")
    @Results({
            @Result(column = "cur_id", property = "curId"),
            @Result(column = "cur_name", property = "curName"),
            @Result(column = "is_pub", property = "isPub")
    })
    List<EduCurriculum> findPriCurByStuId(Long stuId);

    /**
     * 根据学号删除未选课程信息
     *
     * @param eduStu 学生信息
     * @return 响应行数
     */
    @Delete("<script>" +
            "DELETE FROM edu_stu_cur WHERE stu_id = #{stuId} " +
            "<foreach item='item' index='index' collection='pubCurs' " +
            "open=' AND cur_id NOT IN (' separator=',' close=')'> " +
            "#{item.curId}" +
            "</foreach>" +
            "<foreach item='item' index='index' collection='priCurs' " +
            "open=' AND cur_id NOT IN (' separator=',' close=')'> " +
            "#{item.curId}" +
            "</foreach>" +
            "</script>")
    int delStuPriCur(EduStu eduStu);

    /**
     * 根据学号删除选课信息
     *
     * @param id 学号
     * @return 删除条数
     */
    @Delete("DELETE FROM edu_stu_cur WHERE stu_id = #{id}")
    int delCurByStuId(Long id);

    /**
     * 根据课程ID和班级ID查询edu_cur_cls中间表记录数
     *
     * @param id    班级ID
     * @param curId 课程ID
     * @return 记录数
     */
    @Select("SELECT COUNT(*) FROM edu_cur_cls WHERE cur_id = #{curId} AND cls_id = #{id}")
    int findCountCurAndCls(@Param("id") Integer id, @Param("curId") Long curId);

    /**
     * 根据课程ID和班级ID向edu_cur_cls中间表添加信息
     *
     * @param id    班级ID
     * @param curId 课程ID
     */
    @Insert("INSERT edu_cur_cls VALUE (#{curId},#{id})")
    void addCurAndCls(@Param("id") Integer id, @Param("curId") Long curId);

    /**
     * 根据班级ID和课程ID删除中间表edu_cur_cls相关信息
     *
     * @param id    班级ID
     * @param curId 课程ID
     */
    @Delete("DELETE FROM edu_cur_cls WHERE cur_id = #{curId} AND cls_id = #{id}")
    void delCurAndCls(@Param("id") Integer id, @Param("curId") Long curId);

    /**
     * 根据学生ID查找班级ID查找公选必修课程
     *
     * @param stuId 学号
     * @return 公选课
     */
    @Select("SELECT ccs.cur_id, cur_name, is_pub FROM edu_stu stu " +
            "LEFT JOIN edu_dept ed ON stu_rm_id = dept_id " +
            "LEFT JOIN edu_cur_cls ccs ON ccs.cls_id = dept_id " +
            "LEFT JOIN edu_curriculum ec ON ec.cur_id = ccs.cur_id WHERE stu_id = #{stuId}")
    @Results({
            @Result(column = "cur_id", property = "curId"),
            @Result(column = "cur_name", property = "curName"),
            @Result(column = "is_pub", property = "isPub")
    })
    List<EduCurriculum> findPubCurByCls(Long stuId);

    /**
     * 根据学号查询所选课程
     *
     * @param stuId 学号
     * @return 课程列表
     */
    @Select("SELECT " +
            "sc.cur_id, cur_name, is_pub " +
            "FROM edu_stu_cur sc LEFT JOIN edu_curriculum c ON sc.cur_id = c.cur_id WHERE stu_id = #{stuId}")
    @Results({
            @Result(column = "cur_id", property = "curId"),
            @Result(column = "cur_name", property = "curName"),
            @Result(column = "is_pub", property = "isPub")
    })
    List<EduCurriculum> findCurByStuId(Long stuId);

    @Delete("DELETE FROM edu_cur_cls WHERE cur_id = #{id}")
    void deleteCurCls(@Param("id") Long id);
}
